# Import the pandas, pandas_profiling and seaborn libraries
import pandas as pd
import pandas_profiling
import seaborn as sns
# Import the “bi_product.txt” and the “bi_salesFact.txt” files
product=pd.read_table("/Users/Nicolas/Desktop/Masterstudium /Semester 3/Ljubljana/Big Data Management/Datasets/Python/bi_product.txt", sep=";")
fact=pd.read_table("/Users/Nicolas/Desktop/Masterstudium /Semester 3/Ljubljana/Big Data Management/Datasets/Python/bi_salesFact.txt", sep=";")
# Checking if both tables are successfully read
fact
# Checking if both tables are successfully read
product
# Merging the tables (product and fact) with pandas to create a table “total”
total= pd.merge(product, fact, on=["ProductID"])
# Checking if the tables were successfully merged
total
# Getting an overview of the data with profile_report()
total.profile_report()
By using profile_report, specific information about the data will be displayed. For example, that there are 13 attributes and 2,035,538 observations. Of these 13 attributes, 8 are numerical and 5 are categorial. Also it is visible that some values are missing. After this basic overview specific information about the attributes are also displayed. For example the category variable shows that most of the sold products belong to the attribute value “Urban”. Also, the most products are delivered to the city of Miami, whereas the state to which the most products are sent to is California. After the concrete information to every attribute is given, also the correlation between different attributes is displayed. Because some of the values that are displayed are numeric in datatype but have to interpreted as polynomial, the direction of the shown correlation is hard to interpret. For example, there is a negative correlation displayed between the Revenue and the productID. But since the product IDs are numerical in type but have to be used as polynomial, one has to be careful with the interpretation. In general, there seem to be slight correlations between zip codes and longtitue, Revenue and ProductID as well as Revenue and Units.
This observation of the data structure helps to determine which further steps are necessary or possible to perform a valid data analysis. For example, correlations can lead to further investigation or replacing missing values can be crucial to increase the data quality.
Since the overview displayed that there are missing values in the dataset, we have to think about how to solve this problem. At first, we have to figure out how many values are missing and at which attributes.
print(total.isna().sum())
After this step, one knows that there are 280 values missing, all at the attribute “Revenue”. Since the dataset has 2,035,538 observations, it is easiest to just exclude these said observations to increase the data quality.
# Excluding the N/As of the dataset
total_c=total.dropna()
# After this procedure, the dataset has now 2,035,258 observations
total_c
# Grouping the data by Category
total_grouped=total_c.groupby(["Category"], as_index=False)
# Displaying the sum of the sold units and the sum of the revenue for every product
total_grouped["Units","Revenue"].sum()
# Getting the mean of the revenue
total_grouped["Revenue"].mean()
# Getting the median of the revenue
total_grouped["Revenue"].median()
By analyzing the previous tables, it is visible that for every of the four categories the mean revenue is higher than the median revenue. Therefore, a positive skewness is present, signifying that the right tail is longer and that the mass of the distribution is on the left side. As regard to the content this means that the majority of products were sold below the mean price. Some very expensive products were sold in contrast to many “cheaper” goods. The high amount of outliers influences the distribution and is shown through the fact that the mean of the categories is higher than the median. To further analyze this, one can use the max and the mode operator to detect the highest and the most frequent values and determine their distance.
# Calculating the mean value
total_c["Revenue"].mode()
# Calculating the max value
total_c["Revenue"].max()
By using the two previous steps, one can see that the most common value is 419.95. This value is very far away from the highest value 109,719.23. This very big difference shows that the outlier heavily influences the mean. This also is an indicator for the skewness of the distribution.
We want to create graphs to visually display the connection between the categories and the revenue. Since we have one categorial value (Category) and one numerical (Revenue) not every graph is suitable for the analysis of the data.
Therefore we first only produce one scatterplot and one boxplot for descriptive analysis.
# To be able to modify the titles of the graphs, we have to import matplotlib
import matplotlib.pyplot as plt
graph1=sns.scatterplot(x="Segment", y="Revenue", hue="Category", data=total_c);
plt.title("Scatterplot \n Product revenues by segment \n", weight="bold");
plt.show()
graph2=sns.boxplot(x="Segment", y="Revenue", hue="Category", data=total_c);
plt.title("Boxplot \n Product revenues by segment \n", weight="bold");
plt.show()
By creating a scatterplot with revenue on the y-axis, segment on the x-axis and category as facet one can firstly see by the colors of the respecting attributes, which segments belong to which category. The „Mix“ category represents the segment "All Season". The „Rural“ category represents "Productivity" and "Select". The „Youth“ category is only displayed by the „Youth“ segment. Lastly the biggest Revenue contributer "Urban" also consist of the most segments "Moderation", "Regular", "Extreme" and "Convenience".
This plot futhermore shows the revenue for each order, that was placed within each segment. Therefore we get an overview of the structure of the order. Beside the assignment of the segments one can also see the skewness of the distribution visually. All the categories „Mix“, „Urban“ and „Youth“ have outliers that are distant from the mass of the values. Especially within the segment "Moderation" is an expetionally big outlier is detectable. This is also the maximum value that was displayed in one of the previous actions. One can also see that the products of the rural category have the smallest standard deviation wheras Urban has the biggest standard deviation.
By creating a boxplot, we can get the additional information where the median of each segment is and compare this to the distribution. With this done, it is even more visible that there are a lot of products with just small amounts of Revenues. Almost every segment has a median below 1000, but a certain amount of products are also sold for much more. This furthermore is again an indicator for the skewness of the data.
If we want to get a more aggregrated view, we have to group the data again and then apply methods to calculate the average or the sum for each segment.
# Average revenue by Segment
graph3=sns.catplot(x="Segment", y="Revenue", hue="Category", kind="bar", data=total_c);
plt.title("Barplot \n Average revenues by segment \n", weight="bold");
plt.show()
# Total revenue by segment
total_grouped2=total_c.groupby(["Category", "Segment"] , as_index=False)
total_rev=total_grouped2["Revenue"].sum()
graph4=sns.catplot(x="Segment", y="Revenue", hue="Category", kind="bar", data=total_rev);
plt.title("Barplot \n Total revenues by segment \n", weight="bold");
plt.show()
With these two plots it is even more obvious, that the Urban category is the most profitable one for the company. Especially with the total revenue plot, one gets an aggregated view of the data and can see the relation between the product categorties.
To even more get more insights, we can take an isolated look at the most diverse segment "Moderation".
filter1=total_c["Segment"].isin(["Moderation"])
Moderation=total_c[filter1]
M_graph=pd.Series(Moderation["Revenue"], name="Revenue")
graph5=sns.distplot(M_graph, hist=False);
plt.title("Revenue distribution for Moderation \n", weight="bold");
plt.show()
By analyzing the distribution of the „Moderation“ value it is also possible to detect that there are many values of low revenues. Then there is a very large gap and later on we can see some values for revenues above 100,000. This emphasizes the large range of values within the „Moderation“ segment, which needs special caution with the further analysis.
With this descriptive graphs we can show how the data is distributed. This is important for the further steps of the inductive data analysis. Especially the product category „urban“ is very interesting because it seems to be the most profitable but also has the biggest ranges of all categories. By knowing about these ranges and outliers, one can maybe prepare the data properly and therefore get insights that are more valuable. Because we now can state certain specifications of the data, like skewnesses, and we can create hypotheses where connections between the values are and test these afterwards.